package com.vivo.ic.dm;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.vivo.ic.VLog;

class DownloadProviderHelper extends SQLiteOpenHelper {
    
    private static final String TAG = Constants.PRE_TAG + "DownloadDatabaseHelper";

    /** Database filename */
    private static final String DB_NAME = "vdmdownloads.db";
    /** Current database version */
    private static final int DB_VERSION = 4;
    /** Name of table in the database */
    public static final String DB_TABLE = "downloads";
    
    public DownloadProviderHelper(final Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        VLog.i(TAG, "populating new database");
        onUpgrade(db, 0, DB_VERSION);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        
        for (int version = oldVersion + 1; version <= newVersion; version++) {
            upgradeTo(db, version);
        }
    }
    
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        VLog.i(TAG, "onDowngrade(): oldVersion=" + oldVersion + ", newVersion=" + newVersion);

        db.beginTransaction();
        try {
            Cursor c = db.query("sqlite_master", new String[] { "name" }, "type=?", new String[] { "table" }, null,
                    null, null);
            String tableName;
            try {
                while (c != null && c.moveToNext()) {
                    tableName = c.getString(0);
                    if ("android_metadata".equals(tableName)) {
                        continue;
                    }
                    VLog.i(TAG, "drop table " + tableName);
                    db.execSQL("DROP TABLE IF EXISTS " + tableName);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (c != null) {
                    c.close();
                }
            }
            onCreate(db);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            VLog.e(TAG, "drop all table from master failed, drop manually.");
            db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + Downloads.RequestHeaders.HEADERS_DB_TABLE);
            onCreate(db);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void upgradeTo(SQLiteDatabase db, int version) {
        switch (version) {
        case 1:
            createDownloadsTable(db);
            createHeadersTable(db);
            break;
        
        case 2:
            addColumn(db, DB_TABLE, Downloads.Column.PACKAGE_NAME, "TEXT");
            break;
        case 3:
            addColumn(db, DB_TABLE, Downloads.Column.COMPLETE_THREADS, "INTEGER NOT NULL DEFAULT 0");
            break;
        case 4:
            addColumn(db, DB_TABLE, Downloads.Column.CURRENT_SPEED, "INTEGER NOT NULL DEFAULT 0");
            break;
        
        default:
            throw new IllegalStateException("Don't know how to upgrade to " + version);
        }
    }
    
    private void createDownloadsTable(SQLiteDatabase db) {
        try {
            db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
            db.execSQL("CREATE TABLE " + DB_TABLE + "(" +
                    Downloads.Column._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    Downloads.Column.URI + " TEXT, " +
                    Downloads.Column.FILE_NAME_HINT + " TEXT, " +
                    Downloads.Column.DATA + " TEXT, " +
                    Downloads.Column.MIME_TYPE + " TEXT, " +
                    Downloads.Column.VISIBILITY + " INTEGER, " +
                    Downloads.Column.CONTROL + " INTEGER, " +
                    Downloads.Column.STATUS + " INTEGER, " +
                    Downloads.Column.TOTAL_BYTES + " INTEGER, " +
                    Downloads.Column.CURRENT_BYTES + " INTEGER, " +
                    Downloads.Column.TITLE + " TEXT, " +
                    Downloads.Column.DESCRIPTION + " TEXT, " +
                    Downloads.Column.ERROR_MSG + " TEXT, " +
                    Downloads.Column.FAILED_CONNECTIONS + " INTEGER, " +
                    Downloads.Column.LAST_MODIFICATION + " BIGINT, " +
                    Downloads.Column.NOTIFICATION_EXTRAS + " TEXT, " +
                    Downloads.Column.USER_AGENT + " TEXT, " +
                    Downloads.Column.COOKIE_DATA + " TEXT, " +
                    Downloads.Column.REFERER + " TEXT, " +
                    Downloads.Column.ETAG + " TEXT, " + 
                    Downloads.Column.MEDIA_SCANNED + " BOOLEAN, " +
                    Downloads.Column.RETRY_AFTER_X_REDIRECT_COUNT + " INTEGER, " +
                    Downloads.Column.NETWORK_CHANGED + " INTEGER, " +
                    Downloads.Column.DOWNLOAD_TYPE + " INTEGER, " +
                    Downloads.Column.APP_EXTRA_ONE + " TEXT, " +
                    Downloads.Column.APP_EXTRA_TWO + " TEXT, " +
                    Downloads.Column.APP_EXTRA_THREE + " TEXT, " +
                    Downloads.Column.APP_EXTRA_FOUR + " TEXT, " +
                    Downloads.Column.APP_EXTRA_FIVE + " TEXT);");
        } catch (SQLException ex) {
            VLog.e(TAG, "couldn't create table in downloads database");
            throw ex;
        }
    }
    
    private void createHeadersTable(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + Downloads.RequestHeaders.HEADERS_DB_TABLE);
        db.execSQL("CREATE TABLE " + Downloads.RequestHeaders.HEADERS_DB_TABLE + "(" +
                   "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                   Downloads.RequestHeaders.COLUMN_DOWNLOAD_ID + " INTEGER NOT NULL," +
                   Downloads.RequestHeaders.COLUMN_HEADER + " TEXT NOT NULL," +
                   Downloads.RequestHeaders.COLUMN_VALUE + " TEXT NOT NULL" +
                   ");");
    }
    
    private void addColumn(SQLiteDatabase db, String dbTable, String columnName, String columnDefinition) {
        if (!checkColumnExist(db, dbTable, columnName)) {

            db.execSQL("ALTER TABLE " + dbTable + " ADD COLUMN " + columnName + " " + columnDefinition);
        }
    }

    private boolean checkColumnExist(SQLiteDatabase db, String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;
        try {
            // 查询一行
            cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);
            result = cursor != null && cursor.getColumnIndex(columnName) != -1;
        } catch (Exception e) {
            VLog.e(TAG, "checkColumnExist error " + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return result;
    }
}
